package org.dandelion.memo.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import org.dandelion.memo.entity.Memo;
import org.dandelion.memo.entity.MemoCategory;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

public class MemoDao {
    private static final String TAG = "MemoDao";

    private static MemoDao memoDao;

    public static MemoDao getInstance() {
        if (memoDao == null) {
            memoDao = new MemoDao();
        }

        return memoDao;
    }

    public void createTable(SQLiteDatabase db) {
        db.execSQL("create table memo(" +
                "id integer primary key autoincrement," +
                "title text," +
                "content text," +
                "date_time text," +
                "is_favourite integer," +
                "is_deleted integer," +
                "category_id integer," +
                "foreign key (category_id) " +
                "references memo_category(id) " +
                "on delete cascade" +
                ");"
        );

        Log.d("MemoDao", "create table memo.");
        Log.d("MemoDao", db.getClass().getSimpleName());
    }

    public void dropTable(SQLiteDatabase db) {
        db.execSQL("drop table memo;");
    }

    public void save(Memo memo, SQLiteDatabase db) {
        Locale locale = new Locale("zh_CN");
        String sql = String.format(locale,
                "insert into memo " +
                        "(title, content, date_time, is_favourite, is_deleted, category_id) " +
                        "values ('%s', '%s', '%s', %d, %d, %d);",
                memo.getTitle(),
                memo.getContent(),
                memo.getDateTimeAsString(),
                memo.isFavourite() ? 1 : 0,
                memo.isDeleted() ? 1 : 0,
                memo.getCategory().getId()
        );

        Log.d(TAG, sql);
        db.execSQL(sql);
        db.close();
    }

    public List<Memo> findAll(SQLiteDatabase db) {
        String sql = "select " +
                "m.id as id," +
                "m.title as title," +
                "m.content as content," +
                "m.date_time as date_time," +
                "m.is_favourite as is_favourite," +
                "m.is_deleted as is_deleted," +
                "mc.id as category_id," +
                "mc.name as category_name " +
                "from memo m " +
                "inner join memo_category mc " +
                "on m.category_id=mc.id " +
                "order by datetime(m.date_time) desc;";

        Log.d("MemoDao", sql);
        Cursor cursor = db.rawQuery(sql, null);
        Log.d("MemoDao", "items selected num: " + cursor.getCount());

        List<Memo> memos = getMultiRecords(cursor);

        cursor.close();
        db.close();

        return memos;
    }

    public List<Memo> findUndeleted(SQLiteDatabase db) {
        String sql = "select " +
                "m.id as id," +
                "m.title as title," +
                "m.content as content," +
                "m.date_time as date_time," +
                "m.is_favourite as is_favourite," +
                "m.is_deleted as is_deleted," +
                "mc.id as category_id," +
                "mc.name as category_name " +
                "from memo m " +
                "inner join memo_category mc " +
                "on m.category_id=mc.id " +
                "where is_deleted=0 " +
                "order by datetime(m.date_time) desc;";

        Log.d("MemoDao", sql);
        Cursor cursor = db.rawQuery(sql, null);
        Log.d("MemoDao", "items selected num: " + cursor.getCount());

        List<Memo> memos = getMultiRecords(cursor);

        cursor.close();
        db.close();

        return memos;
    }

    public void update(Memo memo, SQLiteDatabase db) {
        String sql = "update memo " +
                "set title=?," +
                "content=?," +
                "date_time=?," +
                "is_favourite=?," +
                "is_deleted=?," +
                "category_id=? " +
                "where id=?";

        db.execSQL(sql, new String[]{
                memo.getTitle(),
                memo.getContent(),
                memo.getDateTimeAsString(),
                memo.isFavourite() ? "1" : "0",
                memo.isDeleted() ? "1" : "0",
                memo.getCategory().getId() + "",
                memo.getId() + ""
        });

        Log.d(TAG, sql);

        db.close();
    }

    public List<Memo> findCollect(SQLiteDatabase db) {
        String sql = "select " +
                "m.id as id," +
                "m.title as title," +
                "m.content as content," +
                "m.date_time as date_time," +
                "m.is_favourite as is_favourite," +
                "m.is_deleted as is_deleted," +
                "mc.id as category_id," +
                "mc.name as category_name " +
                "from memo m " +
                "inner join memo_category mc " +
                "on m.category_id=mc.id " +
                "where m.is_favourite=1 " +
                "and m.is_deleted=0 " +
                "order by datetime(m.date_time) desc;";

        Log.d("MemoDao", sql);
        Cursor cursor = db.rawQuery(sql, null);
        Log.d("MemoDao", "items selected num: " + cursor.getCount());

        List<Memo> memos = getMultiRecords(cursor);

        cursor.close();
        db.close();

        return memos;
    }

    public List<Memo> findDeleted(SQLiteDatabase db) {
        String sql = "select " +
                "m.id as id," +
                "m.title as title," +
                "m.content as content," +
                "m.date_time as date_time," +
                "m.is_favourite as is_favourite," +
                "m.is_deleted as is_deleted," +
                "mc.id as category_id," +
                "mc.name as category_name " +
                "from memo m " +
                "inner join memo_category mc " +
                "on m.category_id=mc.id " +
                "where m.is_deleted=1 " +
                "order by datetime(m.date_time) desc;";

        Log.d("MemoDao", sql);
        Cursor cursor = db.rawQuery(sql, null);
        Log.d("MemoDao", "items selected num: " + cursor.getCount());

        List<Memo> memos = getMultiRecords(cursor);

        cursor.close();
        db.close();

        return memos;
    }

    public Memo findById(int id, SQLiteDatabase db) {
        Cursor cursor = db.rawQuery("select " +
                        "m.id as id," +
                        "m.title as title," +
                        "m.content as content," +
                        "m.date_time as date_time," +
                        "m.is_favourite as is_favourite," +
                        "m.is_deleted as is_deleted," +
                        "mc.id as category_id," +
                        "mc.name as category_name " +
                        "from memo m " +
                        "where m.id='" + id + "' " +
                        "inner join memo_category mc " +
                        "on m.category_id=mc.id " +
                        "order by datetime(m.date_time) desc;",
                null
        );

        Memo memo = getRecord(cursor);

        cursor.close();

        return memo;
    }

    public void deleteById(int id, SQLiteDatabase db) {
        db.execSQL("delete from memo " +
                "where id=" + id + ";");
    }

    private Memo getRecord(Cursor cursor) {
        List<Memo> memoList = getMultiRecords(cursor);
        return memoList.size() > 0 ? memoList.get(0) : null;
    }

    private List<Memo> getMultiRecords(Cursor cursor) {
        List<Memo> memos = new ArrayList<>();

        if (cursor.moveToFirst()) {
            do {
                Memo memo = new Memo();

                MemoCategory category = new MemoCategory();

                memo.setId(cursor.getInt(cursor.getColumnIndex("id")));
                memo.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                memo.setContent(cursor.getString(cursor.getColumnIndex("content")));
                memo.setDateTime(cursor.getString(cursor.getColumnIndex("date_time")));
                memo.setDeleted(cursor.getInt(cursor.getColumnIndex("is_deleted"))
                        != 0);
                memo.setFavourite(cursor.getInt(cursor.getColumnIndex("is_favourite"))
                        != 0);

                category.setId(cursor.getInt(cursor.getColumnIndex("category_id")));
                category.setName(cursor.getString(
                        cursor.getColumnIndex("category_name")));

                memo.setCategory(category);

                memos.add(memo);
            } while (cursor.moveToNext());
        }

        return memos;
    }
}
